/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Assets;
import Address.AddressBean;
import Brand.BrandBean;
import Country.CountryBean;
import Department.DepartmentBean;
import Designation.DesignationBean;
import Employee.EmployeeBean;
import ProductCategory.ProductCategoryBean;
import State.StateBean;
import User.UserBean;
import Vendor.VendorBean;
import java.sql.*;
import java.util.Vector;
/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
    
    public static int add(AssetsBean ab)throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("insert into assets values(assetsid.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,?)");
           
            pre.setString(1, ab.getAssetName());
            pre.setString(2, ab.getSerialNo());
            pre.setInt(3, ab.getAssinedStatus());
            pre.setInt(4, ab.getEmployeeId());
            pre.setDate(5, ab.getAssignedDate());
            pre.setInt(6, ab.getBrandId());
            pre.setInt(7, ab.getVendorId());
            pre.setDate(8, ab.getAccquireDate());
            pre.setDate(9, ab.getWarrantyStart());
            pre.setDate(10, ab.getWarrantyEnd());
            pre.setString(11, ab.getModelNo());
            pre.setString(12, ab.getPictureName());
            pre.setString(13, ab.getAttachmentName());
            pre.setString(14, ab.getDescription());
            pre.setInt(15, ab.getUserId());
            pre.setInt(16, ab.getDepartmentId());
             i=pre.executeUpdate();
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }
         public static int edit(AssetsBean ab)throws SQLException{
        int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("update ASSETS set ASSET_NAME=?, SERIAL_NO=?, ASSIGNED_STATUS=?, EMPLOYEE_ID=?, ASSIGNED_DATE=?, BRAND_ID=?, VENDOR_ID=?, ACCQUIRE_DATE=?, WARRANTY_STARTS=?, WARRNTY_ENDS=?, MODEL_NO=?, PICTURE_NAME=?, ATTACHMENT_NAME=?, DESCRIPTION=?, USER_ID=?, ENTRY_DATE=sysdate, DEPARTMENT_ID=? where ASSET_ID=?");
           
            pre.setString(1, ab.getAssetName());
            pre.setString(2, ab.getSerialNo());
            pre.setInt(3, ab.getAssinedStatus());
            pre.setInt(4, ab.getEmployeeId());
            pre.setDate(5, ab.getAssignedDate());
            pre.setInt(6, ab.getBrandId());
            pre.setInt(7, ab.getVendorId());
            pre.setDate(8, ab.getAccquireDate());
            pre.setDate(9, ab.getWarrantyStart());
            pre.setDate(10, ab.getWarrantyEnd());
            pre.setString(11, ab.getModelNo());
            pre.setString(12, ab.getPictureName());
            pre.setString(13, ab.getAttachmentName());
            pre.setString(14, ab.getDescription());
            pre.setInt(15, ab.getUserId());
            pre.setInt(16, ab.getDepartmentId());
            pre.setInt(17, ab.getAssetId());

            i=pre.executeUpdate();
        }catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }
public static int delete(int id)throws SQLException{
    int i=0;
        try{
            con=DBConnection.Connection.getConnection();
            PreparedStatement pre=con.prepareStatement("delete from assets where ASSET_ID=?");
            pre.setInt(1, id);
            i=pre.executeUpdate();
           }
        catch(Exception e){
            System.out.println(e);
        }finally{
            con.close();
        }return i;
    }


public static Vector viewAll()throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from assets a  left join users u on a.employee_id=u.USER_ID left join employee e on e.user_ID=u.user_id  left join address ad on e.ADDRESS_ID=ad.ADDRESS_ID  left join country c on ad.COUNTRY_ID=c.COUNTRY_ID  left join state s on ad.STATE_ID=s.STATE_ID  left join DESIGNATION ds on e.DESIGNATION_ID=ds.DESIGNATION_ID   left join brand b on a.BRAND_ID=b.BRAND_ID left join vendor ve on a.VENDOR_ID=ve.VENDOR_ID  left join PRODUCT_CATEGORY pc on ve.PRODUCT_CATEGORY_ID=pc.PRODUCT_CATEGORY_ID left join DEPARTMENT d on a.DEPARTMENT_ID=d.DEPARTMENT_ID");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            AssetsBean ab=new AssetsBean();
            EmployeeBean eb=new EmployeeBean();
            AddressBean adb=new AddressBean();
            CountryBean cb=new CountryBean();
            StateBean sb=new StateBean();
            DepartmentBean db=new DepartmentBean();
            DesignationBean dsb=new DesignationBean();
            UserBean ub=new UserBean();
            BrandBean bb=new BrandBean();
            VendorBean vb=new VendorBean();
            ProductCategoryBean pcb=new ProductCategoryBean();
            ab.setAssetId(rs.getInt(1));
            ab.setAssetName(rs.getString(2));
            ab.setSerialNo(rs.getString(3));
            ab.setAssinedStatus(rs.getInt(4));
            ab.setEmployeeId(rs.getInt(5));
            ab.setAssignedDate(rs.getDate(6));
            ab.setBrandId(rs.getInt(7));
            ab.setVendorId(rs.getInt(8));
            ab.setAccquireDate(rs.getDate(9));
            ab.setWarrantyStart(rs.getDate(10));
            ab.setWarrantyEnd(rs.getDate(11));
            ab.setModelNo(rs.getString(12));
            ab.setPictureName(rs.getString(13));
            ab.setAttachmentName(rs.getString(14));
            ab.setDescription(rs.getString(15));
            ab.setUserId(rs.getInt(16));
            ab.setEntryDate(rs.getDate(17));
            ab.setDepartmentId(rs.getInt(18));
            
            ub.setUserId(rs.getInt(19));
            ub.setDor(rs.getDate(20));
            ub.setPassword(rs.getString(21));
            ub.setUserType(rs.getString(22));
            ub.setEmail(rs.getString(23));
            ub.setEmployeeName(rs.getString(24));
            ub.setDob(rs.getDate(25));
            ub.setEntryDate(rs.getDate(26));
            ub.setUserStatus(rs.getInt(27));
            ub.setEntryEmail(rs.getString(28));
            
            eb.setEmployeeId(rs.getInt(29));
            eb.setEmployeeFatherName(rs.getString(30));
            eb.setAddressId(rs.getInt(31));
            eb.setPanCard(rs.getString(32));
            eb.setMobile(rs.getString(33));
            eb.setPhone(rs.getString(34));
            eb.setGender(rs.getString(35));
            eb.setHigherQuali(rs.getString(36));
            eb.setDoj(rs.getDate(37));
            eb.setJobdetails(rs.getString(38));
            eb.setDepartmentId(rs.getInt(39));
            eb.setDesignationId(rs.getInt(40));
            eb.setUserId(rs.getInt(41));
            eb.setPhoto(rs.getString(42));
            eb.setEntryDate(rs.getDate(43));
            eb.setEmployeeType(rs.getInt(44));
            eb.setEmployeeStatus(rs.getInt(45));
            adb.setAddressId(rs.getInt(46));
            adb.setAddress1(rs.getString(47));
            adb.setAddress2(rs.getString(48));
            adb.setCountryId(rs.getInt(49));
            adb.setStateId(rs.getInt(50));
            adb.setPinCode(rs.getString(51));
            adb.setEntryDate(rs.getDate(52));
            cb.setCountryId(rs.getInt(53));
            cb.setCountryName(rs.getString(54));
            cb.setEntryDate(rs.getDate(55));
            sb.setStateId(rs.getInt(56));
            sb.setCountryId(rs.getInt(57));
            sb.setStateName(rs.getString(58));
            sb.setEntryDate(rs.getDate(59));
            adb.setCountryBean(cb);
            adb.setStateBean(sb);
            eb.setAddressBean(adb);
            
            dsb.setDesignationId(rs.getInt(60));
            dsb.setDesignation(rs.getString(61));
            dsb.setEntryDate(rs.getDate(62));
            eb.setDesignationBean(dsb);
            
            eb.setUserBean(ub);
            ab.setEmployeeBean(eb);
            bb.setBrandId(rs.getInt(63));
            bb.setBrandName(rs.getString(64));
            bb.setEntryDate(rs.getDate(65));
            ab.setBrandBean(bb);
            vb.setVendorId(rs.getInt(66));
            vb.setVendorName(rs.getString(67));
            vb.setProductCategoryId(rs.getInt(68));
            vb.setAddress(rs.getString(69));
            vb.setTin(rs.getString(70));
            vb.setMobile(rs.getString(71));
            vb.setPhone(rs.getString(72));
            vb.setEntryDate(rs.getDate(73));
            vb.setEmail(rs.getString(74));
            pcb.setProducCategoryId(rs.getInt(75));
            pcb.setProducCategoryName(rs.getString(76));
            pcb.setEntryDate(rs.getDate(77));
            vb.setProductCategoryBean(pcb);
            db.setDepartmentId(rs.getInt(78));
            db.setDepartmentName(rs.getString(79));
            db.setEntryDate(rs.getDate(80));
            ab.setDepartmentBean(db);
            ab.setVendorBean(vb);
            list.add(ab);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}

public static AssetsBean viewSpecific(String cname, String val)throws SQLException{
    AssetsBean ab=new AssetsBean();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from assets a left join employee e on a.EMPLOYEE_ID=e.EMPLOYEE_ID  left join address ad on e.ADDRESS_ID=ad.ADDRESS_ID  left join country c on ad.COUNTRY_ID=c.COUNTRY_ID  left join state s on ad.STATE_ID=s.STATE_ID  left join DEPARTMENT d on e.DEPARTMENT_ID=d.DEPARTMENT_ID  left join DESIGNATION ds on e.DESIGNATION_ID=ds.DESIGNATION_ID  left join users u on e.USER_ID=u.USER_ID  left join brand b on a.BRAND_ID=b.BRAND_ID left join vendor ve on a.VENDOR_ID=ve.VENDOR_ID  left join PRODUCT_CATEGORY pc on ve.PRODUCT_CATEGORY_ID=pc.PRODUCT_CATEGORY_ID  left join users ue on a.USER_ID=ue.USER_ID where a."+cname+"="+val+"");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            EmployeeBean eb=new EmployeeBean();
            AddressBean adb=new AddressBean();
            CountryBean cb=new CountryBean();
            StateBean sb=new StateBean();
            DepartmentBean db=new DepartmentBean();
            DesignationBean dsb=new DesignationBean();
            UserBean ub=new UserBean();
            BrandBean bb=new BrandBean();
            VendorBean vb=new VendorBean();
            ProductCategoryBean pcb=new ProductCategoryBean();
            ab.setAssetId(rs.getInt(1));
            ab.setAssetName(rs.getString(2));
            ab.setSerialNo(rs.getString(3));
            ab.setAssinedStatus(rs.getInt(4));
            ab.setEmployeeId(rs.getInt(5));
            ab.setAssignedDate(rs.getDate(6));
            ab.setBrandId(rs.getInt(7));
            ab.setVendorId(rs.getInt(8));
            ab.setAccquireDate(rs.getDate(9));
            ab.setWarrantyStart(rs.getDate(10));
            ab.setWarrantyEnd(rs.getDate(11));
            ab.setModelNo(rs.getString(12));
            ab.setPictureName(rs.getString(13));
            ab.setAttachmentName(rs.getString(14));
            ab.setDescription(rs.getString(15));
            ab.setUserId(rs.getInt(16));
            ab.setEntryDate(rs.getDate(17));
            eb.setEmployeeId(rs.getInt(18));
            eb.setEmployeeFatherName(rs.getString(19));
            eb.setAddressId(rs.getInt(20));
            eb.setPanCard(rs.getString(21));
            eb.setMobile(rs.getString(22));
            eb.setPhone(rs.getString(23));
            eb.setGender(rs.getString(24));
            eb.setHigherQuali(rs.getString(25));
            eb.setDoj(rs.getDate(26));
            eb.setJobdetails(rs.getString(27));
            eb.setDepartmentId(rs.getInt(28));
            eb.setUserId(rs.getInt(29));
            eb.setPhoto(rs.getString(30));
            eb.setEntryDate(rs.getDate(31));
            eb.setEmployeeType(rs.getInt(32));
            eb.setEmployeeStatus(rs.getInt(33));
            adb.setAddressId(rs.getInt(34));
            adb.setAddress1(rs.getString(35));
            adb.setAddress2(rs.getString(36));
            adb.setCountryId(rs.getInt(37));
            adb.setStateId(rs.getInt(38));
            adb.setPinCode(rs.getString(39));
            adb.setEntryDate(rs.getDate(40));
            cb.setCountryId(rs.getInt(41));
            cb.setCountryName(rs.getString(42));
            cb.setEntryDate(rs.getDate(43));
            sb.setStateId(rs.getInt(44));
            sb.setCountryId(rs.getInt(45));
            sb.setStateName(rs.getString(46));
            sb.setEntryDate(rs.getDate(47));
            adb.setCountryBean(cb);
            adb.setStateBean(sb);
            eb.setAddressBean(adb);
            db.setDepartmentId(rs.getInt(48));
            db.setDepartmentName(rs.getString(49));
            db.setEntryDate(rs.getDate(50));
            eb.setDepartmentBean(db);
            dsb.setDesignationId(rs.getInt(51));
            dsb.setDesignation(rs.getString(52));
            dsb.setEntryDate(rs.getDate(53));
            eb.setDesignationBean(dsb);
            ub.setUserId(rs.getInt(54));
            ub.setDor(rs.getDate(55));
            ub.setPassword(rs.getString(56));
            ub.setUserType(rs.getString(57));
            ub.setEmail(rs.getString(58));
            ub.setEmployeeName(rs.getString(59));
            ub.setDob(rs.getDate(60));
            ub.setEntryDate(rs.getDate(61));
            ub.setUserStatus(rs.getInt(62));
            ub.setEntryEmail(rs.getString(63));
            eb.setUserBean(ub);
            ab.setEmployeeBean(eb);
            bb.setBrandId(rs.getInt(64));
            bb.setBrandName(rs.getString(65));
            bb.setEntryDate(rs.getDate(66));
            ab.setBrandBean(bb);
            vb.setVendorId(rs.getInt(67));
            vb.setVendorName(rs.getString(68));
            vb.setProductCategoryId(rs.getInt(69));
            vb.setAddress(rs.getString(70));
            vb.setTin(rs.getString(71));
            vb.setMobile(rs.getString(72));
            vb.setPhone(rs.getString(73));
            vb.setEntryDate(rs.getDate(74));
            vb.setEmail(rs.getString(75));
            pcb.setProducCategoryId(rs.getInt(76));
            pcb.setProducCategoryName(rs.getString(77));
            pcb.setEntryDate(rs.getDate(78));
            vb.setProductCategoryBean(pcb);
            ab.setVendorBean(vb);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return ab;
}
public static Vector BrandWiseReport(String val) throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from assets a  left join users u on a.employee_id=u.USER_ID left join employee e on e.user_ID=u.user_id  left join address ad on e.ADDRESS_ID=ad.ADDRESS_ID  left join country c on ad.COUNTRY_ID=c.COUNTRY_ID  left join state s on ad.STATE_ID=s.STATE_ID  left join DESIGNATION ds on e.DESIGNATION_ID=ds.DESIGNATION_ID left join brand b on a.BRAND_ID=b.BRAND_ID left join vendor ve on a.VENDOR_ID=ve.VENDOR_ID  left join PRODUCT_CATEGORY pc on ve.PRODUCT_CATEGORY_ID=pc.PRODUCT_CATEGORY_ID left join DEPARTMENT d on a.DEPARTMENT_ID=d.DEPARTMENT_ID where upper(b.BRAND_NAME) like upper('"+val+"%')");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            AssetsBean ab=new AssetsBean();
            EmployeeBean eb=new EmployeeBean();
            AddressBean adb=new AddressBean();
            CountryBean cb=new CountryBean();
            StateBean sb=new StateBean();
            DepartmentBean db=new DepartmentBean();
            DesignationBean dsb=new DesignationBean();
            UserBean ub=new UserBean();
            BrandBean bb=new BrandBean();
            VendorBean vb=new VendorBean();
            ProductCategoryBean pcb=new ProductCategoryBean();
            ab.setAssetId(rs.getInt(1));
            ab.setAssetName(rs.getString(2));
            ab.setSerialNo(rs.getString(3));
            ab.setAssinedStatus(rs.getInt(4));
            ab.setEmployeeId(rs.getInt(5));
            ab.setAssignedDate(rs.getDate(6));
            ab.setBrandId(rs.getInt(7));
            ab.setVendorId(rs.getInt(8));
            ab.setAccquireDate(rs.getDate(9));
            ab.setWarrantyStart(rs.getDate(10));
            ab.setWarrantyEnd(rs.getDate(11));
            ab.setModelNo(rs.getString(12));
            ab.setPictureName(rs.getString(13));
            ab.setAttachmentName(rs.getString(14));
            ab.setDescription(rs.getString(15));
            ab.setUserId(rs.getInt(16));
            ab.setEntryDate(rs.getDate(17));
            ab.setDepartmentId(rs.getInt(18));
            
            ub.setUserId(rs.getInt(19));
            ub.setDor(rs.getDate(20));
            ub.setPassword(rs.getString(21));
            ub.setUserType(rs.getString(22));
            ub.setEmail(rs.getString(23));
            ub.setEmployeeName(rs.getString(24));
            ub.setDob(rs.getDate(25));
            ub.setEntryDate(rs.getDate(26));
            ub.setUserStatus(rs.getInt(27));
            ub.setEntryEmail(rs.getString(28));
            
            eb.setEmployeeId(rs.getInt(29));
            eb.setEmployeeFatherName(rs.getString(30));
            eb.setAddressId(rs.getInt(31));
            eb.setPanCard(rs.getString(32));
            eb.setMobile(rs.getString(33));
            eb.setPhone(rs.getString(34));
            eb.setGender(rs.getString(35));
            eb.setHigherQuali(rs.getString(36));
            eb.setDoj(rs.getDate(37));
            eb.setJobdetails(rs.getString(38));
            eb.setDepartmentId(rs.getInt(39));
            eb.setDesignationId(rs.getInt(40));
            eb.setUserId(rs.getInt(41));
            eb.setPhoto(rs.getString(42));
            eb.setEntryDate(rs.getDate(43));
            eb.setEmployeeType(rs.getInt(44));
            eb.setEmployeeStatus(rs.getInt(45));
            adb.setAddressId(rs.getInt(46));
            adb.setAddress1(rs.getString(47));
            adb.setAddress2(rs.getString(48));
            adb.setCountryId(rs.getInt(49));
            adb.setStateId(rs.getInt(50));
            adb.setPinCode(rs.getString(51));
            adb.setEntryDate(rs.getDate(52));
            cb.setCountryId(rs.getInt(53));
            cb.setCountryName(rs.getString(54));
            cb.setEntryDate(rs.getDate(55));
            sb.setStateId(rs.getInt(56));
            sb.setCountryId(rs.getInt(57));
            sb.setStateName(rs.getString(58));
            sb.setEntryDate(rs.getDate(59));
            adb.setCountryBean(cb);
            adb.setStateBean(sb);
            eb.setAddressBean(adb);
            
            dsb.setDesignationId(rs.getInt(60));
            dsb.setDesignation(rs.getString(61));
            dsb.setEntryDate(rs.getDate(62));
            eb.setDesignationBean(dsb);
            
            eb.setUserBean(ub);
            ab.setEmployeeBean(eb);
            bb.setBrandId(rs.getInt(63));
            bb.setBrandName(rs.getString(64));
            bb.setEntryDate(rs.getDate(65));
            ab.setBrandBean(bb);
            vb.setVendorId(rs.getInt(66));
            vb.setVendorName(rs.getString(67));
            vb.setProductCategoryId(rs.getInt(68));
            vb.setAddress(rs.getString(69));
            vb.setTin(rs.getString(70));
            vb.setMobile(rs.getString(71));
            vb.setPhone(rs.getString(72));
            vb.setEntryDate(rs.getDate(73));
            vb.setEmail(rs.getString(74));
            pcb.setProducCategoryId(rs.getInt(75));
            pcb.setProducCategoryName(rs.getString(76));
            pcb.setEntryDate(rs.getDate(77));
            vb.setProductCategoryBean(pcb);
            db.setDepartmentId(rs.getInt(78));
            db.setDepartmentName(rs.getString(79));
            db.setEntryDate(rs.getDate(80));
            ab.setDepartmentBean(db);
            ab.setVendorBean(vb);
            list.add(ab);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector DepartmentWiseReport(String val) throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from assets a  left join users u on a.employee_id=u.USER_ID left join employee e on e.user_ID=u.user_id  left join address ad on e.ADDRESS_ID=ad.ADDRESS_ID  left join country c on ad.COUNTRY_ID=c.COUNTRY_ID  left join state s on ad.STATE_ID=s.STATE_ID  left join DESIGNATION ds on e.DESIGNATION_ID=ds.DESIGNATION_ID   left join brand b on a.BRAND_ID=b.BRAND_ID left join vendor ve on a.VENDOR_ID=ve.VENDOR_ID  left join PRODUCT_CATEGORY pc on ve.PRODUCT_CATEGORY_ID=pc.PRODUCT_CATEGORY_ID left join DEPARTMENT d on a.DEPARTMENT_ID=d.DEPARTMENT_ID where upper(d.DEPARTMENT_NAME) like upper('"+val+"%')");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            AssetsBean ab=new AssetsBean();
            EmployeeBean eb=new EmployeeBean();
            AddressBean adb=new AddressBean();
            CountryBean cb=new CountryBean();
            StateBean sb=new StateBean();
            DepartmentBean db=new DepartmentBean();
            DesignationBean dsb=new DesignationBean();
            UserBean ub=new UserBean();
            BrandBean bb=new BrandBean();
            VendorBean vb=new VendorBean();
            ProductCategoryBean pcb=new ProductCategoryBean();
            ab.setAssetId(rs.getInt(1));
            ab.setAssetName(rs.getString(2));
            ab.setSerialNo(rs.getString(3));
            ab.setAssinedStatus(rs.getInt(4));
            ab.setEmployeeId(rs.getInt(5));
            ab.setAssignedDate(rs.getDate(6));
            ab.setBrandId(rs.getInt(7));
            ab.setVendorId(rs.getInt(8));
            ab.setAccquireDate(rs.getDate(9));
            ab.setWarrantyStart(rs.getDate(10));
            ab.setWarrantyEnd(rs.getDate(11));
            ab.setModelNo(rs.getString(12));
            ab.setPictureName(rs.getString(13));
            ab.setAttachmentName(rs.getString(14));
            ab.setDescription(rs.getString(15));
            ab.setUserId(rs.getInt(16));
            ab.setEntryDate(rs.getDate(17));
            ab.setDepartmentId(rs.getInt(18));
            
            ub.setUserId(rs.getInt(19));
            ub.setDor(rs.getDate(20));
            ub.setPassword(rs.getString(21));
            ub.setUserType(rs.getString(22));
            ub.setEmail(rs.getString(23));
            ub.setEmployeeName(rs.getString(24));
            ub.setDob(rs.getDate(25));
            ub.setEntryDate(rs.getDate(26));
            ub.setUserStatus(rs.getInt(27));
            ub.setEntryEmail(rs.getString(28));
            
            eb.setEmployeeId(rs.getInt(29));
            eb.setEmployeeFatherName(rs.getString(30));
            eb.setAddressId(rs.getInt(31));
            eb.setPanCard(rs.getString(32));
            eb.setMobile(rs.getString(33));
            eb.setPhone(rs.getString(34));
            eb.setGender(rs.getString(35));
            eb.setHigherQuali(rs.getString(36));
            eb.setDoj(rs.getDate(37));
            eb.setJobdetails(rs.getString(38));
            eb.setDepartmentId(rs.getInt(39));
            eb.setDesignationId(rs.getInt(40));
            eb.setUserId(rs.getInt(41));
            eb.setPhoto(rs.getString(42));
            eb.setEntryDate(rs.getDate(43));
            eb.setEmployeeType(rs.getInt(44));
            eb.setEmployeeStatus(rs.getInt(45));
            adb.setAddressId(rs.getInt(46));
            adb.setAddress1(rs.getString(47));
            adb.setAddress2(rs.getString(48));
            adb.setCountryId(rs.getInt(49));
            adb.setStateId(rs.getInt(50));
            adb.setPinCode(rs.getString(51));
            adb.setEntryDate(rs.getDate(52));
            cb.setCountryId(rs.getInt(53));
            cb.setCountryName(rs.getString(54));
            cb.setEntryDate(rs.getDate(55));
            sb.setStateId(rs.getInt(56));
            sb.setCountryId(rs.getInt(57));
            sb.setStateName(rs.getString(58));
            sb.setEntryDate(rs.getDate(59));
            adb.setCountryBean(cb);
            adb.setStateBean(sb);
            eb.setAddressBean(adb);
            
            dsb.setDesignationId(rs.getInt(60));
            dsb.setDesignation(rs.getString(61));
            dsb.setEntryDate(rs.getDate(62));
            eb.setDesignationBean(dsb);
            
            eb.setUserBean(ub);
            ab.setEmployeeBean(eb);
            bb.setBrandId(rs.getInt(63));
            bb.setBrandName(rs.getString(64));
            bb.setEntryDate(rs.getDate(65));
            ab.setBrandBean(bb);
            vb.setVendorId(rs.getInt(66));
            vb.setVendorName(rs.getString(67));
            vb.setProductCategoryId(rs.getInt(68));
            vb.setAddress(rs.getString(69));
            vb.setTin(rs.getString(70));
            vb.setMobile(rs.getString(71));
            vb.setPhone(rs.getString(72));
            vb.setEntryDate(rs.getDate(73));
            vb.setEmail(rs.getString(74));
            pcb.setProducCategoryId(rs.getInt(75));
            pcb.setProducCategoryName(rs.getString(76));
            pcb.setEntryDate(rs.getDate(77));
            vb.setProductCategoryBean(pcb);
            db.setDepartmentId(rs.getInt(78));
            db.setDepartmentName(rs.getString(79));
            db.setEntryDate(rs.getDate(80));
            ab.setDepartmentBean(db);
            ab.setVendorBean(vb);
            list.add(ab);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector VendorWiseReport(String val) throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from assets a  left join users u on a.employee_id=u.USER_ID left join employee e on e.user_ID=u.user_id  left join address ad on e.ADDRESS_ID=ad.ADDRESS_ID  left join country c on ad.COUNTRY_ID=c.COUNTRY_ID  left join state s on ad.STATE_ID=s.STATE_ID  left join DESIGNATION ds on e.DESIGNATION_ID=ds.DESIGNATION_ID   left join brand b on a.BRAND_ID=b.BRAND_ID left join vendor ve on a.VENDOR_ID=ve.VENDOR_ID  left join PRODUCT_CATEGORY pc on ve.PRODUCT_CATEGORY_ID=pc.PRODUCT_CATEGORY_ID left join DEPARTMENT d on a.DEPARTMENT_ID=d.DEPARTMENT_ID where upper(ve.VENDOR_NAME) like upper('"+val+"%')");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            AssetsBean ab=new AssetsBean();
            EmployeeBean eb=new EmployeeBean();
            AddressBean adb=new AddressBean();
            CountryBean cb=new CountryBean();
            StateBean sb=new StateBean();
            DepartmentBean db=new DepartmentBean();
            DesignationBean dsb=new DesignationBean();
            UserBean ub=new UserBean();
            BrandBean bb=new BrandBean();
            VendorBean vb=new VendorBean();
            ProductCategoryBean pcb=new ProductCategoryBean();
            ab.setAssetId(rs.getInt(1));
            ab.setAssetName(rs.getString(2));
            ab.setSerialNo(rs.getString(3));
            ab.setAssinedStatus(rs.getInt(4));
            ab.setEmployeeId(rs.getInt(5));
            ab.setAssignedDate(rs.getDate(6));
            ab.setBrandId(rs.getInt(7));
            ab.setVendorId(rs.getInt(8));
            ab.setAccquireDate(rs.getDate(9));
            ab.setWarrantyStart(rs.getDate(10));
            ab.setWarrantyEnd(rs.getDate(11));
            ab.setModelNo(rs.getString(12));
            ab.setPictureName(rs.getString(13));
            ab.setAttachmentName(rs.getString(14));
            ab.setDescription(rs.getString(15));
            ab.setUserId(rs.getInt(16));
            ab.setEntryDate(rs.getDate(17));
            ab.setDepartmentId(rs.getInt(18));
            
            ub.setUserId(rs.getInt(19));
            ub.setDor(rs.getDate(20));
            ub.setPassword(rs.getString(21));
            ub.setUserType(rs.getString(22));
            ub.setEmail(rs.getString(23));
            ub.setEmployeeName(rs.getString(24));
            ub.setDob(rs.getDate(25));
            ub.setEntryDate(rs.getDate(26));
            ub.setUserStatus(rs.getInt(27));
            ub.setEntryEmail(rs.getString(28));
            
            eb.setEmployeeId(rs.getInt(29));
            eb.setEmployeeFatherName(rs.getString(30));
            eb.setAddressId(rs.getInt(31));
            eb.setPanCard(rs.getString(32));
            eb.setMobile(rs.getString(33));
            eb.setPhone(rs.getString(34));
            eb.setGender(rs.getString(35));
            eb.setHigherQuali(rs.getString(36));
            eb.setDoj(rs.getDate(37));
            eb.setJobdetails(rs.getString(38));
            eb.setDepartmentId(rs.getInt(39));
            eb.setDesignationId(rs.getInt(40));
            eb.setUserId(rs.getInt(41));
            eb.setPhoto(rs.getString(42));
            eb.setEntryDate(rs.getDate(43));
            eb.setEmployeeType(rs.getInt(44));
            eb.setEmployeeStatus(rs.getInt(45));
            adb.setAddressId(rs.getInt(46));
            adb.setAddress1(rs.getString(47));
            adb.setAddress2(rs.getString(48));
            adb.setCountryId(rs.getInt(49));
            adb.setStateId(rs.getInt(50));
            adb.setPinCode(rs.getString(51));
            adb.setEntryDate(rs.getDate(52));
            cb.setCountryId(rs.getInt(53));
            cb.setCountryName(rs.getString(54));
            cb.setEntryDate(rs.getDate(55));
            sb.setStateId(rs.getInt(56));
            sb.setCountryId(rs.getInt(57));
            sb.setStateName(rs.getString(58));
            sb.setEntryDate(rs.getDate(59));
            adb.setCountryBean(cb);
            adb.setStateBean(sb);
            eb.setAddressBean(adb);
            
            dsb.setDesignationId(rs.getInt(60));
            dsb.setDesignation(rs.getString(61));
            dsb.setEntryDate(rs.getDate(62));
            eb.setDesignationBean(dsb);
            
            eb.setUserBean(ub);
            ab.setEmployeeBean(eb);
            bb.setBrandId(rs.getInt(63));
            bb.setBrandName(rs.getString(64));
            bb.setEntryDate(rs.getDate(65));
            ab.setBrandBean(bb);
            vb.setVendorId(rs.getInt(66));
            vb.setVendorName(rs.getString(67));
            vb.setProductCategoryId(rs.getInt(68));
            vb.setAddress(rs.getString(69));
            vb.setTin(rs.getString(70));
            vb.setMobile(rs.getString(71));
            vb.setPhone(rs.getString(72));
            vb.setEntryDate(rs.getDate(73));
            vb.setEmail(rs.getString(74));
            pcb.setProducCategoryId(rs.getInt(75));
            pcb.setProducCategoryName(rs.getString(76));
            pcb.setEntryDate(rs.getDate(77));
            vb.setProductCategoryBean(pcb);
            db.setDepartmentId(rs.getInt(78));
            db.setDepartmentName(rs.getString(79));
            db.setEntryDate(rs.getDate(80));
            ab.setDepartmentBean(db);
            ab.setVendorBean(vb);
            list.add(ab);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector NameWiseReport(String val) throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from assets a  left join users u on a.employee_id=u.USER_ID left join employee e on e.user_ID=u.user_id  left join address ad on e.ADDRESS_ID=ad.ADDRESS_ID  left join country c on ad.COUNTRY_ID=c.COUNTRY_ID  left join state s on ad.STATE_ID=s.STATE_ID  left join DESIGNATION ds on e.DESIGNATION_ID=ds.DESIGNATION_ID   left join brand b on a.BRAND_ID=b.BRAND_ID left join vendor ve on a.VENDOR_ID=ve.VENDOR_ID  left join PRODUCT_CATEGORY pc on ve.PRODUCT_CATEGORY_ID=pc.PRODUCT_CATEGORY_ID left join DEPARTMENT d on a.DEPARTMENT_ID=d.DEPARTMENT_ID where upper(a.ASSET_NAME) like upper('"+val+"%')");
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            AssetsBean ab=new AssetsBean();
            EmployeeBean eb=new EmployeeBean();
            AddressBean adb=new AddressBean();
            CountryBean cb=new CountryBean();
            StateBean sb=new StateBean();
            DepartmentBean db=new DepartmentBean();
            DesignationBean dsb=new DesignationBean();
            UserBean ub=new UserBean();
            BrandBean bb=new BrandBean();
            VendorBean vb=new VendorBean();
            ProductCategoryBean pcb=new ProductCategoryBean();
            ab.setAssetId(rs.getInt(1));
            ab.setAssetName(rs.getString(2));
            ab.setSerialNo(rs.getString(3));
            ab.setAssinedStatus(rs.getInt(4));
            ab.setEmployeeId(rs.getInt(5));
            ab.setAssignedDate(rs.getDate(6));
            ab.setBrandId(rs.getInt(7));
            ab.setVendorId(rs.getInt(8));
            ab.setAccquireDate(rs.getDate(9));
            ab.setWarrantyStart(rs.getDate(10));
            ab.setWarrantyEnd(rs.getDate(11));
            ab.setModelNo(rs.getString(12));
            ab.setPictureName(rs.getString(13));
            ab.setAttachmentName(rs.getString(14));
            ab.setDescription(rs.getString(15));
            ab.setUserId(rs.getInt(16));
            ab.setEntryDate(rs.getDate(17));
            ab.setDepartmentId(rs.getInt(18));
            
            ub.setUserId(rs.getInt(19));
            ub.setDor(rs.getDate(20));
            ub.setPassword(rs.getString(21));
            ub.setUserType(rs.getString(22));
            ub.setEmail(rs.getString(23));
            ub.setEmployeeName(rs.getString(24));
            ub.setDob(rs.getDate(25));
            ub.setEntryDate(rs.getDate(26));
            ub.setUserStatus(rs.getInt(27));
            ub.setEntryEmail(rs.getString(28));
            
            eb.setEmployeeId(rs.getInt(29));
            eb.setEmployeeFatherName(rs.getString(30));
            eb.setAddressId(rs.getInt(31));
            eb.setPanCard(rs.getString(32));
            eb.setMobile(rs.getString(33));
            eb.setPhone(rs.getString(34));
            eb.setGender(rs.getString(35));
            eb.setHigherQuali(rs.getString(36));
            eb.setDoj(rs.getDate(37));
            eb.setJobdetails(rs.getString(38));
            eb.setDepartmentId(rs.getInt(39));
            eb.setDesignationId(rs.getInt(40));
            eb.setUserId(rs.getInt(41));
            eb.setPhoto(rs.getString(42));
            eb.setEntryDate(rs.getDate(43));
            eb.setEmployeeType(rs.getInt(44));
            eb.setEmployeeStatus(rs.getInt(45));
            adb.setAddressId(rs.getInt(46));
            adb.setAddress1(rs.getString(47));
            adb.setAddress2(rs.getString(48));
            adb.setCountryId(rs.getInt(49));
            adb.setStateId(rs.getInt(50));
            adb.setPinCode(rs.getString(51));
            adb.setEntryDate(rs.getDate(52));
            cb.setCountryId(rs.getInt(53));
            cb.setCountryName(rs.getString(54));
            cb.setEntryDate(rs.getDate(55));
            sb.setStateId(rs.getInt(56));
            sb.setCountryId(rs.getInt(57));
            sb.setStateName(rs.getString(58));
            sb.setEntryDate(rs.getDate(59));
            adb.setCountryBean(cb);
            adb.setStateBean(sb);
            eb.setAddressBean(adb);
            
            dsb.setDesignationId(rs.getInt(60));
            dsb.setDesignation(rs.getString(61));
            dsb.setEntryDate(rs.getDate(62));
            eb.setDesignationBean(dsb);
            
            eb.setUserBean(ub);
            ab.setEmployeeBean(eb);
            bb.setBrandId(rs.getInt(63));
            bb.setBrandName(rs.getString(64));
            bb.setEntryDate(rs.getDate(65));
            ab.setBrandBean(bb);
            vb.setVendorId(rs.getInt(66));
            vb.setVendorName(rs.getString(67));
            vb.setProductCategoryId(rs.getInt(68));
            vb.setAddress(rs.getString(69));
            vb.setTin(rs.getString(70));
            vb.setMobile(rs.getString(71));
            vb.setPhone(rs.getString(72));
            vb.setEntryDate(rs.getDate(73));
            vb.setEmail(rs.getString(74));
            pcb.setProducCategoryId(rs.getInt(75));
            pcb.setProducCategoryName(rs.getString(76));
            pcb.setEntryDate(rs.getDate(77));
            vb.setProductCategoryBean(pcb);
            db.setDepartmentId(rs.getInt(78));
            db.setDepartmentName(rs.getString(79));
            db.setEntryDate(rs.getDate(80));
            ab.setDepartmentBean(db);
            ab.setVendorBean(vb);
            list.add(ab);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
public static Vector ExpiryReport(String dfro, String dtoo) throws SQLException{
    Vector<Object> list=new Vector<Object>();
    try{
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select * from assets a  left join users u on a.employee_id=u.USER_ID left join employee e on e.user_ID=u.user_id  left join address ad on e.ADDRESS_ID=ad.ADDRESS_ID  left join country c on ad.COUNTRY_ID=c.COUNTRY_ID  left join state s on ad.STATE_ID=s.STATE_ID  left join DESIGNATION ds on e.DESIGNATION_ID=ds.DESIGNATION_ID   left join brand b on a.BRAND_ID=b.BRAND_ID left join vendor ve on a.VENDOR_ID=ve.VENDOR_ID  left join PRODUCT_CATEGORY pc on ve.PRODUCT_CATEGORY_ID=pc.PRODUCT_CATEGORY_ID left join DEPARTMENT d on a.DEPARTMENT_ID=d.DEPARTMENT_ID where a.WARRNTY_ENDS between to_date(?) and to_date(?)");
        pre.setString(1, dfro);
        pre.setString(2, dtoo);
        ResultSet rs=pre.executeQuery();
        while(rs.next()){
            AssetsBean ab=new AssetsBean();
            EmployeeBean eb=new EmployeeBean();
            AddressBean adb=new AddressBean();
            CountryBean cb=new CountryBean();
            StateBean sb=new StateBean();
            DepartmentBean db=new DepartmentBean();
            DesignationBean dsb=new DesignationBean();
            UserBean ub=new UserBean();
            BrandBean bb=new BrandBean();
            VendorBean vb=new VendorBean();
            ProductCategoryBean pcb=new ProductCategoryBean();
            ab.setAssetId(rs.getInt(1));
            ab.setAssetName(rs.getString(2));
            ab.setSerialNo(rs.getString(3));
            ab.setAssinedStatus(rs.getInt(4));
            ab.setEmployeeId(rs.getInt(5));
            ab.setAssignedDate(rs.getDate(6));
            ab.setBrandId(rs.getInt(7));
            ab.setVendorId(rs.getInt(8));
            ab.setAccquireDate(rs.getDate(9));
            ab.setWarrantyStart(rs.getDate(10));
            ab.setWarrantyEnd(rs.getDate(11));
            ab.setModelNo(rs.getString(12));
            ab.setPictureName(rs.getString(13));
            ab.setAttachmentName(rs.getString(14));
            ab.setDescription(rs.getString(15));
            ab.setUserId(rs.getInt(16));
            ab.setEntryDate(rs.getDate(17));
            ab.setDepartmentId(rs.getInt(18));
            
            ub.setUserId(rs.getInt(19));
            ub.setDor(rs.getDate(20));
            ub.setPassword(rs.getString(21));
            ub.setUserType(rs.getString(22));
            ub.setEmail(rs.getString(23));
            ub.setEmployeeName(rs.getString(24));
            ub.setDob(rs.getDate(25));
            ub.setEntryDate(rs.getDate(26));
            ub.setUserStatus(rs.getInt(27));
            ub.setEntryEmail(rs.getString(28));
            
            eb.setEmployeeId(rs.getInt(29));
            eb.setEmployeeFatherName(rs.getString(30));
            eb.setAddressId(rs.getInt(31));
            eb.setPanCard(rs.getString(32));
            eb.setMobile(rs.getString(33));
            eb.setPhone(rs.getString(34));
            eb.setGender(rs.getString(35));
            eb.setHigherQuali(rs.getString(36));
            eb.setDoj(rs.getDate(37));
            eb.setJobdetails(rs.getString(38));
            eb.setDepartmentId(rs.getInt(39));
            eb.setDesignationId(rs.getInt(40));
            eb.setUserId(rs.getInt(41));
            eb.setPhoto(rs.getString(42));
            eb.setEntryDate(rs.getDate(43));
            eb.setEmployeeType(rs.getInt(44));
            eb.setEmployeeStatus(rs.getInt(45));
            adb.setAddressId(rs.getInt(46));
            adb.setAddress1(rs.getString(47));
            adb.setAddress2(rs.getString(48));
            adb.setCountryId(rs.getInt(49));
            adb.setStateId(rs.getInt(50));
            adb.setPinCode(rs.getString(51));
            adb.setEntryDate(rs.getDate(52));
            cb.setCountryId(rs.getInt(53));
            cb.setCountryName(rs.getString(54));
            cb.setEntryDate(rs.getDate(55));
            sb.setStateId(rs.getInt(56));
            sb.setCountryId(rs.getInt(57));
            sb.setStateName(rs.getString(58));
            sb.setEntryDate(rs.getDate(59));
            adb.setCountryBean(cb);
            adb.setStateBean(sb);
            eb.setAddressBean(adb);
            
            dsb.setDesignationId(rs.getInt(60));
            dsb.setDesignation(rs.getString(61));
            dsb.setEntryDate(rs.getDate(62));
            eb.setDesignationBean(dsb);
            
            eb.setUserBean(ub);
            ab.setEmployeeBean(eb);
            bb.setBrandId(rs.getInt(63));
            bb.setBrandName(rs.getString(64));
            bb.setEntryDate(rs.getDate(65));
            ab.setBrandBean(bb);
            vb.setVendorId(rs.getInt(66));
            vb.setVendorName(rs.getString(67));
            vb.setProductCategoryId(rs.getInt(68));
            vb.setAddress(rs.getString(69));
            vb.setTin(rs.getString(70));
            vb.setMobile(rs.getString(71));
            vb.setPhone(rs.getString(72));
            vb.setEntryDate(rs.getDate(73));
            vb.setEmail(rs.getString(74));
            pcb.setProducCategoryId(rs.getInt(75));
            pcb.setProducCategoryName(rs.getString(76));
            pcb.setEntryDate(rs.getDate(77));
            vb.setProductCategoryBean(pcb);
            db.setDepartmentId(rs.getInt(78));
            db.setDepartmentName(rs.getString(79));
            db.setEntryDate(rs.getDate(80));
            ab.setDepartmentBean(db);
            ab.setVendorBean(vb);
            list.add(ab);
        }
    }catch(Exception e){
        System.out.println(e);
    }finally{
        con.close();
    }return list;
}
}
